using System;
using System.Data;
using System.Collections;
using System.Data.SQLite;
using System.Reflection;
using System.Collections.Generic;

namespace DataBaseTool
{
    class DATABASE_DAO
    {
        public DATABASE_DAO()
        {
        }
        SuperDAO super = new SuperDAO();

        public DataTable select(DATABASE database)
        {
            Dictionary<string, string> m = new Dictionary<string, string>(); 



            string sql = "";

            sql += " select * from database ";
            
            string where = "";
            PropertyInfo[] PropertyList = database.GetType().GetProperties();
            foreach (PropertyInfo item in PropertyList)
            {
                string name = item.Name;
                object value = item.GetValue(database, null);
                if (value != null)
                {
                    where += name+"='"+value.ToString()+"' and ";
                }
            }
            
            if (!string.IsNullOrEmpty(sql))
            {
               where=where.Substring(0, where.Length - 5);
                sql +=" where "+ where;
            }

            Console.WriteLine(sql);

            DataTable dt = super.Select(sql);
            return dt;
        }

        public int save(DATABASE database)
        {
            SQLiteConnection con = super.getConnection();
            con.Open();
            string sql = "insert into database ( name,value ) values (@name,@value) ; ";
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            cmd.Parameters.Add("@name", DbType.String).Value = database.name;
            cmd.Parameters.Add("@value", DbType.String).Value = database.value;
            cmd.ExecuteNonQuery();
            cmd = new SQLiteCommand("select @@IDENTITY as 'id'", con);
            int id = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            con.Dispose();
            cmd = null;
            return id;
        }

        public void update(DATABASE database)
        {
            SQLiteConnection con = super.getConnection();
            con.Open();
            string sql = "update database set name=@name,value=@value where id=@id;";
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            cmd.Parameters.Add("@id", DbType.Int64).Value = database.id;
            cmd.Parameters.Add("@name", DbType.String).Value = database.name;
            cmd.Parameters.Add("@value", DbType.String).Value = database.value;
            cmd.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            cmd = null;
        }

        public void delete(DATABASE database)
        {
            SQLiteConnection con = super.getConnection();
            con.Open();
            string sql = " delete from database where id=@id";
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            cmd.Parameters.Add("@id", DbType.Int64).Value = database.id;
            cmd.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            cmd = null;
        }

    }
}